package com.zzrc.grape.util;

import java.sql.*;

public final class JDBCUtil {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://192.168.15.31:3306/peony?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true";
    private static final String USER = "qa";
    private static final String  PASSWORD = "pAqk5wPY9L48QMxd";

   static PreparedStatement statement = null;

    private JDBCUtil(){}

    static {
        /**
         * 驱动注册
         */
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
     * 获取 Connetion
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException{
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    /**
     * 释放资源
     * @param conn
     * @param st
     * @param rs
     */
    public static void colseResource(Connection conn,Statement st,ResultSet rs) {
        closeResultSet(rs);
        closeStatement(st);
        closeConnection(conn);
    }

    /**
     * 释放连接 Connection
     * @param conn
     */
    public static void closeConnection(Connection conn) {
        if(conn !=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //等待垃圾回收
        conn = null;
    }

    /**
     * 释放语句执行者 Statement
     * @param st
     */
    public static void closeStatement(Statement st) {
        if(st !=null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //等待垃圾回收
        st = null;
    }

    /**
     * 释放结果集 ResultSet
     * @param rs
     */
    public static void closeResultSet(ResultSet rs) {
        if(rs !=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //等待垃圾回收
        rs = null;
    }

    /**
     * 执行查询sql语句
     *
     * @param sql
     * @return
     */
    public static ResultSet selectSQL(String sql, Connection conn, String str) throws SQLException {
        ResultSet rs = null;
        try {
            statement = conn.prepareStatement(sql);
            statement.setString(1, str);
            rs = statement.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    /**
     * 执行插入sql语句
     *
     * @param sql
     * @return
     */
    static boolean insertSQL(String sql, Connection conn) {
        try {
            statement = conn.prepareStatement(sql);
            statement.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.out.println("插入数据库时出错：");
            e.printStackTrace();
        } catch (Exception e) {
            System.out.println("插入时出错：");
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 执行删除sql语句
     *
     * @param sql
     * @return
     */
    public static boolean deleteSQL(String sql, Connection conn, String str) {
        try {
            statement = conn.prepareStatement(sql);
            statement.setString(1, str);
            statement.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.out.println("删除数据库时出错：");
            e.printStackTrace();
        } catch (Exception e) {
            System.out.println("删除时出错：");
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 执行更新sql语句
     *
     * @param sql
     * @return
     */
    public static boolean updateSQL(String sql, Connection conn, String str) {
        try {
            statement = conn.prepareStatement(sql);
            statement.setString(1, str);
            statement.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.out.println("更新数据库时出错：");
            e.printStackTrace();
        } catch (Exception e) {
            System.out.println("更新时出错：");
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 打印结果集
     *
     * 具体列根据自己的数据库表结构更改
     *
     * @param rs
     */
    static void print(ResultSet rs) {
        System.out.println("-----------------");
        System.out.println("查询结果:");
        System.out.println("-----------------");

        try {
            while (rs.next()) {
                System.out.println(rs.getInt(0) + "/t/t" + rs.getString(1)
                        + "/t/t" + rs.getString(2));
            }
        } catch (SQLException e) {
            System.out.println("显示时数据库出错。");
            e.printStackTrace();
        } catch (Exception e) {
            System.out.println("显示出错。");
            e.printStackTrace();
        }
    }

}